﻿using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
namespace INTEX.DataBase.Tools
{
    public partial class INTEXDBC
    {
        public DataSet QueryQPSTEP(DataRow dwData, int option = 1)
        {
            DataSet SReturn = new DataSet();
            DataSet SResult = new DataSet();
            OracleCommand or1;
             
                or1 = OracleDB.CreateCommand();
                switch (option)
                {
                    case 1:
                        or1.CommandText = "SELECT M.QPMKURZB,M.QPMMEEH,M.QPMBEZ,S.* "
                            + " FROM QPSTEP S,QPMERKM M"
                            + " WHERE S.QPSMERKM=M.QPMMERKM "
                            + " AND     QPSPNRKRS=:QPSPNRKRS AND QPSPLFDNR=:QPSPLFDNR";
                        or1.Parameters.Add("QPSPNRKRS", OracleType.NVarChar).Value = dwData["QPSPNRKRS"].ToString();
                        or1.Parameters.Add("QPSPLFDNR", OracleType.NVarChar).Value = dwData["QPSPLFDNR"].ToString();
                        break;
                    case 2:
                        or1.CommandText = "SELECT M.QPMKURZB,M.QPMMEEH,M.QPMBEZ,QPMNORM,WAPBEM, WAPBIS,WAPME,WAPMIN,WAPMAX,S.*"
                            + "  FROM QPSTEP S LEFT JOIN QPMERKM M ON S.QPSMERKM=M.QPMMERKM"
                            + " LEFT JOIN WAPROF ON RTRIM(QPSMERKM)= RTRIM(WAPANFART)  AND WAPART=:WAPART AND WAPVAR=:WAPVAR"
                            + " WHERE QPSPNRKRS=:QPSPNRKRS AND  QPSPLFDNR=:QPSPLFDNR"
                            + " ORDER BY QPSSTEP";
                        or1.Parameters.Add("QPSPNRKRS", OracleType.NVarChar).Value = dwData["QPZPNRKRS"].ToString();
                        or1.Parameters.Add("QPSPLFDNR", OracleType.NVarChar).Value = dwData["QPZPLFDNR"].ToString();
                        or1.Parameters.Add("WAPART", OracleType.NVarChar).Value = dwData["QPZART"].ToString();
                        or1.Parameters.Add("WAPVAR", OracleType.NVarChar).Value = Left(dwData["QPZVAR"].ToString(), 2);
                        break;
                    case 3:
                        or1.CommandText = "SELECT M.QPMKURZB,M.QPMMEEH,M.QPMBEZ,QPMNORM,WAPBEM, WAPBIS,WAPME,WAPMIN,WAPMAX,QPSMANDANT,QPSPNRKRS,QPSPLFDNR,QPSSTEP, "
                        + " QPSAENZL,QPSAENB,QPSAEND,QPSNEUB,QPSNEUD,QPSPRFGRP,QPSARTFB,QPSAGANG,QPSGENKZ,QPSMERKM,QPSPMGRP,QPSDYNTAB,QPSPGROE,QPSFEHLANT,QPSMESSANZ, "
                        + " QPSFORMEL,QPSERFART,QPSZEIT,QPSMENGE,QPSKSTKRS,QPSKST,QPSRAGANG,QPSSPGRD,NVL(TO_CHAR(TRIM(WAPBEM)*1000),QPSNSWERT) AS QPSNSWERT, "
                        + " NVL(TRIM(WAPMIN),QPSNSUG) AS QPSNSUG, "
                        + " NVL(TRIM(WAPMAX),QPSNSOG) AS QPSNSOG,QPSNAUS,QPSASWERT,QPSASUG,QPSASOG,QPSAAUS, "
                        + " QPSNEUG,QPSNEOG,QPSDRUCKKZ,QPSUGKZ,QPSOGKZ,QPSCWEIGHT,QPSFPROCCO,QPSWE,QPSPKSTKRS,QPSPKST,QPSFILLER "
                            + "  FROM QPSTEP S LEFT JOIN QPMERKM M ON S.QPSMERKM=M.QPMMERKM"
                            + " LEFT JOIN WAPROF ON RTRIM(QPSMERKM)= RTRIM(WAPANFART)  AND WAPART=:WAPART AND WAPVAR=:WAPVAR AND WAPWAKZ = 'F' "
                            + " WHERE QPSPNRKRS=:QPSPNRKRS AND  QPSPLFDNR=:QPSPLFDNR"
                            + " ORDER BY QPSSTEP";
                        or1.Parameters.Add("QPSPNRKRS", OracleType.NVarChar).Value = dwData["QPZPNRKRS"].ToString();
                        or1.Parameters.Add("QPSPLFDNR", OracleType.NVarChar).Value = dwData["QPZPLFDNR"].ToString();
                        or1.Parameters.Add("WAPART", OracleType.NVarChar).Value = dwData["QPZART"].ToString();
                        or1.Parameters.Add("WAPVAR", OracleType.NVarChar).Value = Left(dwData["QPZVAR"].ToString(), 2);
                        break;
                    default:
                        break;
                }
                SResult = Connect2Proxy(or1);
                if (SResult.Tables.Count > 0)
                {
                    SResult.Tables[0].TableName = "QPSTEP";
                }
                SReturn.Merge(SResult);
        
            return SReturn;
        }
    }
}
